﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace EntityFramework
{
    public class StoredProcedures
    {
        // Instance fields
        private DbWebsite _db;

        public StoredProcedures(DbWebsite db)
        {
            _db = db;
        }

        protected DbWebsite Database
        {
            get { return _db; }
        }

        /// <summary>
        /// Mapping data reader to entity collection
        ///	</summary>
        /// <param name="command">The IDb command.</param>
        ///	<value>The entity list.</value>
        protected List<T> GetList<T>(IDbCommand command)
        {
            return _db.GetList<T>(command);
        }


        public List<ZoneProduct> GetListZoneProduct()
        {
            var sb = new StringBuilder();
            sb.AppendLine("SELECT ");
            sb.AppendLine("[Extent1].[ZoneId] AS [ZoneId],  ");
            sb.AppendLine("[Extent1].[title] AS [title],  ");
            sb.AppendLine("[Extent1].[url] AS [url],  ");
            sb.AppendLine("[Extent1].[ParentId] AS [ParentId],  ");
            sb.AppendLine("[Extent1].[status] AS [status] ");
            sb.AppendLine("FROM [ZoneProduct] AS [Extent1] order by Zoneid asc");
            IDbCommand cmd = _db.CreateCommand(sb.ToString(), false);
            List<ZoneProduct> list = GetList<ZoneProduct>(cmd);
            return list;
        }

        public List<ZoneProduct> GetListZoneProductByCompanyId(int companyID)
        {
            var sb = new StringBuilder();
            //sb.AppendLine("SELECT ");
            //sb.AppendLine("[Extent1].[ZoneId] AS [ZoneId],  ");
            //sb.AppendLine("[Extent1].[title] AS [title],  ");
            //sb.AppendLine("[Extent1].[url] AS [url],  ");
            //sb.AppendLine("[Extent1].[ParentId] AS [ParentId],  ");
            //sb.AppendLine("[Extent1].[status] AS [status] ");
            //sb.AppendLine("FROM [ZoneProduct] AS [Extent1] ");
            //sb.AppendFormat("WHERE [Extent1].CompanyID like '%{0}%,' and [Extent1].[ParentId] <> 0 and  [Extent1].[status]  =1", companyID);
            sb.AppendLine("SELECT Products.ZoneIdOther, ");
            sb.AppendLine("Products.CompanyID, ");
            sb.AppendLine("Companies.NameCp ");
            sb.AppendLine("FROM Products INNER JOIN ");
            sb.AppendLine(" Companies ON Products.CompanyID = Companies.CompanyID ");
            sb.AppendFormat(" WHERE (Products.CompanyID = {0}) and Products.status=1 ", companyID);
            sb.AppendLine(" GROUP BY Products.ZoneIdOther, Products.CompanyID, Companies.NameCp ");
            IDbCommand cmd = _db.CreateCommand(sb.ToString(), false);
            List<ZoneProduct> list = GetList<ZoneProduct>(cmd);
            return list;
        }

        public ZoneProduct GetZoneProductDetailByUrl(string url)
        {
            var sb = new StringBuilder();
            sb.AppendLine("SELECT ");
            sb.AppendLine("[Extent1].[ZoneId] AS [ZoneId],  ");
            sb.AppendLine("[Extent1].[title] AS [title],  ");
            sb.AppendLine("[Extent1].[url] AS [url],  ");
            sb.AppendLine("[Extent1].[ParentId] AS [ParentId],  ");
            sb.AppendLine("[Extent1].[status] AS [status] ");
            sb.AppendLine("FROM [ZoneProduct] AS [Extent1] ");
            sb.AppendFormat("WHERE [Extent1].url = '{0}' ", url);
            IDbCommand cmd = _db.CreateCommand(sb.ToString(), false);
            ZoneProduct list = GetList<ZoneProduct>(cmd).SingleOrDefault();
            return list;
        }

        public List<Product> GetTopProductIsFocusHome(int top, int zoneId = 0, int companyID = 0)
        {
            var sb = new StringBuilder();
            sb.AppendFormat("SELECT TOP({0}) ", top);
            sb.AppendLine("[Extent1].[id] AS [id],  ");
            sb.AppendLine("[Extent1].[ZoneId] AS [ZoneId],  ");
            sb.AppendLine("[Extent1].[ZoneIdOther] AS [ZoneIdOther],  ");
            sb.AppendLine("[Extent1].[Title] AS [Title],  ");
            sb.AppendLine("[Extent1].[Sapo] AS [Sapo],  ");
            sb.AppendLine("[Extent1].[Avatar] AS [Avatar],  ");
            sb.AppendLine("[Extent1].[Author] AS [Author],  ");
            sb.AppendLine("[Extent1].[IsFocus] AS [IsFocus],  ");
            sb.AppendLine("[Extent1].[DistributionDate] AS [DistributionDate],  ");
            sb.AppendLine("[Extent1].[Url] AS [Url],  ");
            sb.AppendLine("[Extent1].[Tags] AS [Tags],  ");
            sb.AppendLine("[Extent1].[status] AS [status],  ");
            sb.AppendLine("[Extent1].[LastModifiedDate] AS [LastModifiedDate],  ");
            sb.AppendLine("[Extent1].[longContent] AS [longContent],  ");
            sb.AppendLine("[Extent1].[price] AS [price],  ");
            sb.AppendLine("[Extent1].[sale] AS [sale],  ");
            sb.AppendLine("[Extent1].[CompanyID] AS [CompanyID],  ");
            sb.AppendLine("[Extent1].[TypeSale] AS [TypeSale],  ");
            sb.AppendLine("[Extent1].[CountVisit] AS [CountVisit], ");
            sb.AppendLine("[Extent1].[CheckQuantity] AS [CheckQuantity],  ");
            sb.AppendLine("[Extent1].[EndDateNew] AS [EndDateNew],  ");
            sb.AppendLine("[Extent1].[Warranty] AS [Warranty],  ");
            sb.AppendLine("[Extent1].[FilterConditions] AS [FilterConditions],  ");
            sb.AppendLine("[Extent1].[Specifications] AS [Specifications],  ");
            sb.AppendLine("[Extent1].[NewsRelation] AS [NewsRelation],  ");
            sb.AppendLine("[Extent1].[ProductCode] AS [ProductCode] ");
            sb.AppendLine("FROM [Products] AS [Extent1] ");
            sb.AppendLine("WHERE [Extent1].IsFocus = 1 AND [Extent1].Status = 1 ");
            if (companyID > 0)
                sb.AppendFormat("AND CompanyID  = {0}", companyID);
            sb.AppendFormat("AND ( {0} = 0 ", zoneId);
            sb.AppendFormat("AND [Extent1].ZoneId = {0} ", zoneId);
            sb.AppendFormat("OR '{0}' IN ([Extent1].[ZoneIdOther]) ", zoneId);
            sb.AppendLine(" ) ");
            sb.AppendLine("ORDER BY [Extent1].[id] DESC ");
            IDbCommand cmd = _db.CreateCommand(sb.ToString(), false);
            List<Product> list = GetList<Product>(cmd);
            return list;
        }

        public List<Product> GetPagingProductByZoneProduct(int pageIndex, int pageSize, int zoneId)
        {
            var sb = new StringBuilder();
            int start=(pageIndex - 1) * pageSize + 1;
            int end = pageIndex*pageSize;
            //sb.AppendLine("DECLARE @S({0} - 1) * {1} + 1tartRow INT ");
            //sb.AppendFormat("SET @StartRow = ({0} - 1) * {1} + 1 DECLARE @EndRow INT ", pageIndex, pageSize);
            //sb.AppendFormat("SET @EndRow = {0} * {1}; WITH temp AS ", pageIndex, pageSize);
            sb.AppendLine("select * from (	 ");
            sb.AppendLine("SELECT TOP("+end+") ");
            sb.AppendLine("[Extent1].[id] AS [id],  ");
            sb.AppendLine("[Extent1].[ZoneId] AS [ZoneId],  ");
            sb.AppendLine("[Extent1].[ZoneIdOther] AS [ZoneIdOther],  ");
            sb.AppendLine("[Extent1].[Title] AS [Title],  ");
            sb.AppendLine("[Extent1].[Sapo] AS [Sapo],  ");
            sb.AppendLine("[Extent1].[Avatar] AS [Avatar],  ");
            sb.AppendLine("[Extent1].[Author] AS [Author],  ");
            sb.AppendLine("[Extent1].[IsFocus] AS [IsFocus],  ");
            sb.AppendLine("[Extent1].[DistributionDate] AS [DistributionDate],  ");
            sb.AppendLine("[Extent1].[Url] AS [Url],  ");
            sb.AppendLine("[Extent1].[Tags] AS [Tags],  ");
            sb.AppendLine("[Extent1].[status] AS [status],  ");
            sb.AppendLine("[Extent1].[LastModifiedDate] AS [LastModifiedDate],  ");
            sb.AppendLine("[Extent1].[longContent] AS [longContent],  ");
            sb.AppendLine("[Extent1].[price] AS [price],  ");
            sb.AppendLine("[Extent1].[sale] AS [sale],  ");
            sb.AppendLine("[Extent1].[CompanyID] AS [CompanyID],  ");
            sb.AppendLine("[Extent1].[TypeSale] AS [TypeSale],  ");
            sb.AppendLine("[Extent1].[CountVisit] AS [CountVisit], ");
            sb.AppendLine("[Extent1].[CheckQuantity] AS [CheckQuantity],  ");
            sb.AppendLine("[Extent1].[EndDateNew] AS [EndDateNew],  ");
            sb.AppendLine("[Extent1].[Warranty] AS [Warranty],  ");
            sb.AppendLine("[Extent1].[FilterConditions] AS [FilterConditions],  ");
            sb.AppendLine("[Extent1].[Specifications] AS [Specifications],  ");
            sb.AppendLine("[Extent1].[NewsRelation] AS [NewsRelation],  ");
            sb.AppendLine("[Extent1].[ProductCode] AS [ProductCode] ");
            sb.AppendLine(", ROW_NUMBER() OVER (ORDER BY [Extent1].[id] DESC) AS 'RowNumber' ");
            sb.AppendLine("FROM [Products] AS [Extent1] ");
            sb.AppendLine("WHERE [Extent1].status = 1 ");
            if (zoneId != 9999)
            {
                sb.AppendFormat("AND ( [Extent1].ZoneId = {0} ", zoneId);
                sb.AppendFormat("OR '{0},' IN ([Extent1].[ZoneIdOther]) ", zoneId);
                sb.AppendLine(" ) ");
            }
            sb.AppendLine(") as tem ");
            sb.AppendLine(" WHere RowNumber BETWEEN "+start+" AND "+end+"  ");


            IDbCommand cmd = _db.CreateCommand(sb.ToString(), false);
            List<Product> list = GetList<Product>(cmd);
            return list;
        }

        public int GetPagingNumber(int zoneId)
        {
            var sb = new StringBuilder();
            sb.AppendLine("SELECT COUNT(0) ");
            sb.AppendLine("FROM [Products] AS [Extent1] ");
            sb.AppendFormat("WHERE [Extent1].status = 1 ");
            if (zoneId != 9999)
            {
                sb.AppendFormat(" AND ( [Extent1].ZoneId = {0} ", zoneId);
                sb.AppendFormat("OR '{0},' IN ([Extent1].[ZoneIdOther]) ", zoneId);
                sb.AppendLine(" ) ");
            }

            IDbCommand cmd = _db.CreateCommand(sb.ToString(), false);
            var list = GetList<int>(cmd).SingleOrDefault();
            return list;
        }

        public int GetCountVisit(long CompanyID)
        {
            var sb = new StringBuilder();
            sb.AppendFormat("UPDATE Products SET CountVisit = CountVisit + 1 WHERE id = {0}", CompanyID);
            sb.AppendLine("SELECT CountVisit ");
            sb.AppendLine("FROM [Products]");
            sb.AppendFormat("WHERE status = 1 ");
            sb.AppendFormat("AND id  = {0}", CompanyID);
            IDbCommand cmd = _db.CreateCommand(sb.ToString(), false);
            var list = GetList<int>(cmd).SingleOrDefault();
            return list;
        }

        public Product GetProductDetail(long productID)
        {
            var sb = new StringBuilder();
            sb.AppendLine("SELECT TOP(1) ");
            sb.AppendLine("[Extent1].[id] AS [id],  ");
            sb.AppendLine("[Extent1].[ZoneId] AS [ZoneId],  ");
            sb.AppendLine("[Extent1].[ZoneIdOther] AS [ZoneIdOther],  ");
            sb.AppendLine("[Extent1].[Title] AS [Title],  ");
            sb.AppendLine("[Extent1].[Sapo] AS [Sapo],  ");
            sb.AppendLine("[Extent1].[Avatar] AS [Avatar],  ");
            sb.AppendLine("[Extent1].[Author] AS [Author],  ");
            sb.AppendLine("[Extent1].[IsFocus] AS [IsFocus],  ");
            sb.AppendLine("[Extent1].[DistributionDate] AS [DistributionDate],  ");
            sb.AppendLine("[Extent1].[Url] AS [Url],  ");
            sb.AppendLine("[Extent1].[Tags] AS [Tags],  ");
            sb.AppendLine("[Extent1].[status] AS [status],  ");
            sb.AppendLine("[Extent1].[LastModifiedDate] AS [LastModifiedDate],  ");
            sb.AppendLine("[Extent1].[longContent] AS [longContent],  ");
            sb.AppendLine("[Extent1].[price] AS [price],  ");
            sb.AppendLine("[Extent1].[sale] AS [sale],  ");
            sb.AppendLine("[Extent1].[CompanyID] AS [CompanyID],  ");
            sb.AppendLine("[Extent1].[TypeSale] AS [TypeSale],  ");
            sb.AppendLine("[Extent1].[CountVisit] AS [CountVisit], ");
            sb.AppendLine("[Extent1].[CheckQuantity] AS [CheckQuantity],  ");
            sb.AppendLine("[Extent1].[EndDateNew] AS [EndDateNew],  ");
            sb.AppendLine("[Extent1].[Warranty] AS [Warranty],  ");
            sb.AppendLine("[Extent1].[FilterConditions] AS [FilterConditions],  ");
            sb.AppendLine("[Extent1].[Specifications] AS [Specifications],  ");
            sb.AppendLine("[Extent1].[NewsRelation] AS [NewsRelation],  ");
            sb.AppendLine("[Extent1].[ProductCode] AS [ProductCode] ");
            sb.AppendLine("FROM [Products] AS [Extent1] ");
            sb.AppendFormat("WHERE [Extent1].[id] = {0} ", productID);
            IDbCommand cmd = _db.CreateCommand(sb.ToString(), false);
            var list = GetList<Product>(cmd).SingleOrDefault();
            return list;
        }

        public List<Product> GetRelationProduct(string productIds)
        {
            var sb = new StringBuilder();
            sb.AppendLine("SELECT ");
            sb.AppendLine("[Extent1].[id] AS [id],  ");
            sb.AppendLine("[Extent1].[ZoneId] AS [ZoneId],  ");
            sb.AppendLine("[Extent1].[ZoneIdOther] AS [ZoneIdOther],  ");
            sb.AppendLine("[Extent1].[Title] AS [Title],  ");
            sb.AppendLine("[Extent1].[Sapo] AS [Sapo],  ");
            sb.AppendLine("[Extent1].[Avatar] AS [Avatar],  ");
            sb.AppendLine("[Extent1].[Author] AS [Author],  ");
            sb.AppendLine("[Extent1].[IsFocus] AS [IsFocus],  ");
            sb.AppendLine("[Extent1].[DistributionDate] AS [DistributionDate],  ");
            sb.AppendLine("[Extent1].[Url] AS [Url],  ");
            sb.AppendLine("[Extent1].[Tags] AS [Tags],  ");
            sb.AppendLine("[Extent1].[status] AS [status],  ");
            sb.AppendLine("[Extent1].[LastModifiedDate] AS [LastModifiedDate],  ");
            sb.AppendLine("[Extent1].[longContent] AS [longContent],  ");
            sb.AppendLine("[Extent1].[price] AS [price],  ");
            sb.AppendLine("[Extent1].[sale] AS [sale],  ");
            sb.AppendLine("[Extent1].[CompanyID] AS [CompanyID],  ");
            sb.AppendLine("[Extent1].[TypeSale] AS [TypeSale],  ");
            sb.AppendLine("[Extent1].[CountVisit] AS [CountVisit], ");
            sb.AppendLine("[Extent1].[CheckQuantity] AS [CheckQuantity],  ");
            sb.AppendLine("[Extent1].[EndDateNew] AS [EndDateNew],  ");
            sb.AppendLine("[Extent1].[Warranty] AS [Warranty],  ");
            sb.AppendLine("[Extent1].[FilterConditions] AS [FilterConditions],  ");
            sb.AppendLine("[Extent1].[Specifications] AS [Specifications],  ");
            sb.AppendLine("[Extent1].[NewsRelation] AS [NewsRelation],  ");
            sb.AppendLine("[Extent1].[ProductCode] AS [ProductCode] ");
            sb.AppendLine("FROM [Products] AS [Extent1] ");
            sb.AppendFormat("WHERE [Extent1].[id] IN ({0}) ", productIds.TrimEnd(','));
            sb.AppendLine("ORDER BY [Extent1].[id] DESC ");
            IDbCommand cmd = _db.CreateCommand(sb.ToString(), false);
            List<Product> list = GetList<Product>(cmd);
            return list;
        }

        public List<Employee> GetListSpecifications(string specifications)
        {
            var sb = new StringBuilder();
            sb.AppendLine("SELECT  ");
            sb.AppendLine("[Extent1].[ID] AS [ID],  ");
            sb.AppendLine("[Extent1].[Name] AS [Name],  ");
            sb.AppendLine("[Extent1].[Email] AS [Email],  ");
            sb.AppendLine("[Extent1].[Tel] AS [Tel],  ");
            sb.AppendLine("[Extent1].[Address] AS [Address],  ");
            sb.AppendLine("[Extent1].[Birthday] AS [Birthday],  ");
            sb.AppendLine("[Extent1].[Descriptions] AS [Descriptions],  ");
            sb.AppendLine("[Extent1].[InfomationOther] AS [InfomationOther],  ");
            sb.AppendLine("[Extent1].[Status] AS [Status],  ");
            sb.AppendLine("[Extent1].[Sex] AS [Sex] ");
            sb.AppendLine("FROM [Employee] AS [Extent1] ");
            sb.AppendFormat("WHERE [Extent1].[ID] IN ({0}) ", specifications.TrimEnd(','));
            IDbCommand cmd = _db.CreateCommand(sb.ToString(), false);
            List<Employee> list = GetList<Employee>(cmd);
            return list;
        }

        public List<DownloadProduct> GetList(long productID)
        {
            var sb = new StringBuilder();
            sb.AppendLine("SELECT  ");
            sb.AppendLine("[Extent1].[DownloadID] AS [DownloadID],  ");
            sb.AppendLine("[Extent1].[id] AS [id],  ");
            sb.AppendLine("[Extent1].[LinkDownload] AS [LinkDownload],  ");
            sb.AppendLine("[Extent1].[ShortContent] AS [ShortContent],  ");
            sb.AppendLine("[Extent1].[Status] AS [Status],  ");
            sb.AppendLine("[Extent1].[Dateadded] AS [Dateadded],  ");
            sb.AppendLine("[Extent1].[TypeProduct] AS [TypeProduct],  ");
            sb.AppendLine("[Extent1].[Title] AS [Title] ");
            sb.AppendLine("FROM [DownloadProduct] AS [Extent1] ");
            sb.AppendFormat("WHERE [Extent1].[id] = {0} ", productID);
            IDbCommand cmd = _db.CreateCommand(sb.ToString(), false);
            List<DownloadProduct> list = GetList<DownloadProduct>(cmd);
            return list;

        }

        public List<Company> GetCompaniesByZoneId(int zoneID)
        {
            var sb = new StringBuilder();
            sb.AppendLine("SELECT   Companies.CompanyID, ");
            sb.AppendLine("         Companies.NameCp,");
            sb.AppendLine("         Companies.Website,");
            sb.AppendLine("         Companies.Status,");
            sb.AppendLine("         Companies.Note,");
            sb.AppendLine("         Companies.Email,");
            sb.AppendLine("         Companies.Logo");
            sb.AppendLine("FROM     Companies INNER JOIN");
            sb.AppendLine("                      Products ON Companies.CompanyID = Products.CompanyID INNER JOIN");
            sb.AppendLine("                      ZoneProduct ON Products.ZoneId = ZoneProduct.ZoneId");
            sb.AppendFormat("WHERE	(ZoneProduct.ZoneId = {0} OR Products.ZoneIdOther = '{0},' )", zoneID);
            sb.AppendLine("GROUP BY Companies.CompanyID, Companies.NameCp, Companies.Website, Companies.Status, Companies.Note, Companies.Email, Companies.Logo");
            IDbCommand cmd = _db.CreateCommand(sb.ToString(), false);
            List<Company> list = GetList<Company>(cmd);
            return list;
        }

        public List<Company> GetCompaniesByZoneIds(int zoneIDs)
        {
            var sb = new StringBuilder();
            //sb.AppendLine("SELECT   Companies.CompanyID, ");
            //sb.AppendLine("         Companies.NameCp,");
            //sb.AppendLine("         Companies.Website,");
            //sb.AppendLine("         Companies.Status,");
            //sb.AppendLine("         Companies.Note,");
            //sb.AppendLine("         Companies.Email,");
            //sb.AppendLine("         Companies.Logo,");
            //sb.AppendLine("         Companies.Url");
            //sb.AppendLine("FROM     Companies ");
            //sb.AppendFormat("WHERE	CompanyID in ({0})", zoneIDs);
            sb.AppendLine("SELECT Companies.CompanyID, ");
            sb.AppendLine("Companies.NameCp,");
            sb.AppendLine("Products.ZoneId");
            sb.AppendLine("FROM  Companies INNER JOIN Products ON Companies.CompanyID = Products.CompanyID ");
            sb.AppendFormat("WHERE     (Products.ZoneId = {0}) AND (Companies.Status = 1) AND Products.status=1 ", zoneIDs);
            sb.AppendLine("GROUP BY Companies.CompanyID, Companies.NameCp,Products.ZoneId order by Companies.CompanyID ");
            IDbCommand cmd = _db.CreateCommand(sb.ToString(), false);
            List<Company> list = GetList<Company>(cmd);
            return list;
        }

        public Company GetCompaniesByID(int companyID)
        {
            var sb = new StringBuilder();
            sb.AppendLine("SELECT   Companies.CompanyID, ");
            sb.AppendLine("         Companies.NameCp,");
            sb.AppendLine("         Companies.Website,");
            sb.AppendLine("         Companies.Status,");
            sb.AppendLine("         Companies.Note,");
            sb.AppendLine("         Companies.Email,");
            sb.AppendLine("         Companies.Logo,");
            sb.AppendLine("         Companies.Url");
            sb.AppendLine("FROM     Companies ");
            sb.AppendFormat("WHERE	CompanyID = {0}", companyID);
            IDbCommand cmd = _db.CreateCommand(sb.ToString(), false);
            var list = GetList<Company>(cmd).SingleOrDefault();
            return list;
        }
    }
}
